let mysql=require('mysql');
// 创建连接池，效率更高，不需要每次操作数据库都创建连接
let pool=mysql.createPool({
    host:'localhost',
    user:'root',
    password:'admin',
    database:'db_web',
    port:3306,
    connectionLimit:50,//允许连接数
    multipleStatements : true,  //是否允许执行多条sql语句
    timezone:"08:00" //大坑，必须加这一句，否则时间不对劲
})
//封装数据库sql请求操作，返回的是一个包含对象的数组
let Query=( sql , ...params )=>{
     return new Promise(function(resolve,reject){
         //从连接池中拿一条链接
         pool.getConnection(function(err,connection){
            if(err){
                return reject(err);
            }
            connection.query( sql , params , function(error,res){
                // console.log(res);
                connection.release();
                if(error){
                    return  reject(error);
                }
                resolve(res);
            });
        });
    });
 };

//用户sql语句

    //登录验证
    let login = function(username,password){
        let sql = `
        select
            *
        from
            message
        where
            message.username='${username}' and  message.password='${password}'
        `;
        return sql;
    }
    //获取cet4单词库
    let cet4 = function(){
      let sql = `SELECT * FROM cet where level = 4`;
      return sql;
    }
    //获取cet6单词库
    let cet6 = function(){
      let sql = `SELECT * FROM cet where level = 6`;
      return sql;
    }
    //更新cet词库熟练度
    let updateCet = function(engOrId,degree){
      var sql;
      if(typeof(engOrId)=='number'){
        sql = `UPDATE cet SET degree = degree + '${degree}' WHERE id = '${engOrId}';`
      }else{
        sql = `UPDATE cet SET degree = degree + '${degree}' WHERE english = '${engOrId}';`
      }
      return sql;
    }
    //获取文章内容
    let getMessage = function(){
      let sql = `SELECT * FROM tb_message`;
      return sql;
    }
    //获取在学单词id
    let highlight = function(){
      let sql = 'SELECT id FROM `tb_msg_word` WHERE english IN (SELECT english FROM cet)';
      return sql;
    }
    //翻译单词
    let transWord = function(word){
      let sql = `select translation from enwords where word = '${word}'`;
      return sql;
    }
 module.exports={
    Query,

    login,
    cet4,
    updateCet,
    cet6,
    getMessage,
    transWord,
    highlight,
 }